This invention relates in general to computer database processing and more specifically to a system providing for drill-down of associated information among two or more databases.
The ability to efficiently access data is important in many aspects of business, education, entertainment and other types of computer applications. As the volume and type of data increases, providing users with tools to make access, analysis, modification, management and other use of data becomes increasingly important.
One type of database access method is “relational” accessing. In relational accessing, a user can form a query to obtain information from a relational database. The query is written in a specialized language that is typically unique to a particular database type. Different database types, and languages, are provided by, e.g., different manufacturers such as Oracle, International Business Machines (IBM), Microsoft, etc. One characteristic that relational databases provide (even among different types) is the ability to form a query based using keywords and logical operators such as AND, OR and NOT.
A database server receives the query and accesses a data store, accordingly, to obtain the desired information. In a relational type of database, the query results in checking of data records, objects or other data items to determine which data items meet the query definition. Since the checking is done in real-time, and on-demand, the processing burdens placed on database servers can become extremely severe, especially when a database is large, has many relationships among data, and when the query is complex.
A second type of database access method is referred to as On-Line Analytical Processing (OLAP) accessing. In OLAP accessing, data in a relational type of database is pre-processed prior to the time a user performs OLAP accessing. The pre-processing allows an administrator to select different ways to organize the data so that it is more meaningful, and useful, to users of the OLAP interface. For example, an administrator may know that a given business is often interested in reviewing promotional sales of products in different merchandising channels. In this case, the administrator may direct the OLAP system to pre-compute tables, or other collections of data, that include promotions, sales, products and channels, in various arrangements and presentations along with other data.
Once the presentations have been pre-computed, the user can instantly access data of the pre-computed variety in different ways. Thus, OLAP differs from the relational type of accessing because in OLAP the results of a query, or other user access action, do not always require computing presentations, or views, from “raw” data items. The time savings can be enormous, on the order of minutes, hours or days. Another benefit is that an overall database system can operate with many more users since each user's actions result in much less drain on system resources such as processor cycles, memory or storage space, etc.
One drawback to the OLAP approach is that if a user requires a database result that has not been pre-computed then the system must generate the result from items in the relational database. Another is that the size of an OLAP database is exponentially proportional to the number of items it holds data for, frequently making storage of detailed information impractical. The result is that OLAP and Relational technologies are complimentary and there is a strong motivation to allow navigation back and forth, based on the business question that needs answering. However, a problem arises in “mapping” user views of data in OLAP with items of data in a relational database. Often, the higher-level of presentation in an OLAP user interface does not map directly to the base items that were used to generate the OLAP presentation. For example, at the OLAP level the user might view a list of “promotional sales” by “product brands” in 2001. The user may then request to view a list of promotional sales for individual products. The OLAP interface will attempt to fulfill the user's request only to discover that it doesn't store data on individual products
The OLAP interface can ask the user to refine the request, can attempt to use default parameters or rules, or take other action to intelligently respond to the user's request. As a user makes increasingly detailed requests for information from OLAP interfaces, the OLAP software must delve deeper and deeper into the data and relationships defined in a relational database from which the OLAP pre-computed presentations were derived. This “delving” deeper into the foundation of the data is referred to as a “drill-down” approach. Ultimately the user may be forced to use relational accessing to obtain the desired information.
Naturally, if the OLAP interface can map higher-level reports, views, or other presentations to relational database information without burdening the user, the more efficient and desirable is the OLAP interface. However, the design of efficient and intuitive interfaces is complicated since today's database applications often use multiple different types of databases among which coherent accessing is desired. For example, there may be multiple relational databases and multiple OLAP databases. Not only do users desire to be able to map, or use drill-down, from any OLAP database to any relational database, but also to map from an OLAP database to another OLAP database; from a relational database to another relational database, or between any different databases, in general. Not only do the number of mapping combinations add to the drill-down demands, but modern systems include databases of different types using different query languages, naming conventions and organizations of data, so that meaningful, accurate, efficient and reliable mappings become extremely difficult—and yet are very desirable and valuable.
Prior art database systems provide drill-through from OLAP to relational by building a metadata model. However, the metadata model only supports OLAP to relational drill-through. Other prior art systems can drill between multiple document types using name matching instead of a metadata map. Still other prior art systems use “relational partitions” as a mechanism to allow OLAP queries to access the relational data under a cube. Unfortunately, regardless of the technology employed these prior art systems are limited in the number of supported drill-through scenarios between OLAP and relational and are unable to provide drill-through from OLAP to OLAP or from relational to OLAP.